<?php
namespace Think;
class Excel {
  public function prepareDownloadExcel($results, $headers, $row_keys, $excel_name = '票务订单', $headers_types=array()) {
    Vendor('PHPExcel.PHPExcel');


    $objPHPExcel = new \PHPExcel();    
    $objProps = $objPHPExcel->getProperties();    
    $objProps->setCreator("Vicky");
    $objProps->setLastModifiedBy("Vicky");    
    $objProps->setTitle("Vicky Contact");    
    $objProps->setSubject("Vicky Contact Data");    
    $objProps->setDescription("Vicky Contact Data");    
    $objProps->setKeywords("Vicky Contact");    
    $objProps->setCategory("Vicky");
    $objPHPExcel->setActiveSheetIndex(0);     
    $objActSheet = $objPHPExcel->getActiveSheet(); 
       
    $objActSheet->setTitle('Sheet1');
    $col_idx = 'A';
    $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(16);
    foreach ($headers as $header) {
      $objActSheet->setCellValue($col_idx++.'1', $header);  
    }
    
    $i = 2;
    foreach ($results as $rlst) {
      $col_idx = 'A';
      foreach ($row_keys as $rk) {
        if(isset($headers_types[$rk])){
            $type = $headers_types[$rk];
        }else{
            $type = 'text';
        }

        switch ($type) {
            case 'text':
                $objActSheet->setCellValueExplicit($col_idx++.$i, $rlst[$rk], \PHPExcel_Cell_DataType::TYPE_STRING);
                break;
            case 'number':
                $objActSheet->setCellValueExplicit($col_idx++.$i, $rlst[$rk], \PHPExcel_Cell_DataType::TYPE_NUMERIC);
                break;
            case 'image':
                //$objActSheet->setCellValueExplicit($col_idx++.$i, $rlst[$rk], PHPExcel_Cell_DataType::TYPE_STRING);
                $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(48);
                if($rlst[$rk]){
                    $objDrawing = new \PHPExcel_Worksheet_Drawing();
                    $objDrawing->setPath($rlst[$rk]);
                    $objDrawing->setCoordinates($col_idx++.$i);
                    $objDrawing->setHeight(100);
                    $maxHeight = 100;
                    $maxWidth = 100;
                    $offsetX =$maxWidth - $objDrawing->getWidth();
                    $objDrawing->setOffsetX($offsetX/2);
                    $offsetY =$maxHeight - $objDrawing->getHeight();
                    $objDrawing->setOffsetY($offsetY/2);
                    $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());   
                }else{
                    $col_idx++;
                }
                 
                break;
            default:
                # code...
                break;
        }
        // $objActSheet->setCellValue($col_idx++.$i, $rlst[$rk]); 
        
      }
      $i++;
    } 

    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    
    ob_end_clean();
    // Redirect output to a client’s web browser (Excel2007)
    $excel_name = iconv("utf-8","gb2312",$excel_name);
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $excel_name . '_' . date('Y-m-d',time()) . '.xlsx"');
    header('Cache-Control: max-age=0');
    // If you're serving to IE 9, then the following may be needed
    header('Cache-Control: max-age=1');

    // If you're serving to IE over SSL, then the following may be needed
    header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
    header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
    header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
    header ('Pragma: public'); // HTTP/1.0
    $objWriter->save('php://output'); 
    exit;
  }


  public function import() {
    Vendor('PHPExcel.PHPExcel');
    $json = array();
    // if ($this->validate()) {
      // if (!empty($this->request->files['file']['name']) && is_file($this->request->files['file']['tmp_name'])) {
      //   // Sanitize the filename
      //   $filename = basename(html_entity_decode($this->request->files['file']['name'], ENT_QUOTES, 'UTF-8'));
      //   // Validate the filename length
      //   if ((utf8_strlen($filename) < 3) || (utf8_strlen($filename) > 128)) {
      //     $json['error'] = '文件名过短';
      //   }
      //   // Allowed file extension types
      //   $allowed = array('xls','xlsx');
      //   if (!in_array(strtolower(substr(strrchr($filename, '.'), 1)), $allowed)) {
      //     $json['error'] = '请上传xls或者xlsx文件';
      //   }
      //   // Return any upload error
      //   if ($this->request->files['file']['error'] != UPLOAD_ERR_OK) {
      //     $json['error'] = '上传出现错误';
      //   }
      // } 
    // }
    // else {
      // $json['error'] = '上传失败';
    // }

    if (!$json) {
      $objPHPExcel = new \PHPExcel(); 
      // $this->load->library('PHPExcel/PHPExcel');
      // $savePath = $this->request->files['file']['tmp_name'];
      $savePath = $_FILES['playinfo']['tmp_name'];
      //$PHPExcel = new PHPExcel();
      $PHPReader = new \PHPExcel_Reader_Excel2007();
      if(!$PHPReader->canRead($savePath)){
        $PHPReader = new \PHPExcel_Reader_Excel5();
      }
      $PHPExcel = $PHPReader->load($savePath);
      $currentSheet = $PHPExcel->getSheet(0);
      $allRow = $currentSheet->getHighestRow();
      //获取 title,penname,nameremark,editor,writerinfo,represent,keyword,oneword,people,outline 在excel中的index
      $title=$penname = $nameremark = $editor = $writerinfo = $represent = $keyword = $oneword = $people = $outline = $follow = $pageview = 0;
      $tmp_idx = 'A'; //假设导入表格不会长于Z
      $currentRow = 1;
      while(($title === 0 || $penname === 0 || $nameremark === 0 || $register===0 || $editor === 0||$writerinfo === 0 ||$represent === 0 ||$keyword === 0 ||$oneword === 0 ||$people === 0||$outline === 0||$follow === 0||$pageview === 0)&&$tmp_idx<'Z') {
        switch (trim((String)$currentSheet->getCell($tmp_idx.$currentRow)->getValue())) {
          case '项目名称':
            $title = $tmp_idx;
            break;
          case '作者':
            $penname = $tmp_idx;
            break;
          case '版权方':
            $nameremark = $tmp_idx;
            break;
          case '版权登记信息':
            $register = $tmp_idx;
            break;
          case '策划编辑':
            $editor = $tmp_idx;
            break;
          case '作者简介':
            $writerinfo = $tmp_idx;
            break;
          case '代表作品':
            $represent = $tmp_idx;
            break;
          case '关键字':
            $keyword = $tmp_idx;
            break;
          case '一句话介绍':
            $oneword = $tmp_idx;
            break;
          case '人物小传':
            $people = $tmp_idx;
            break;
          case '项目梗概':
            $outline = $tmp_idx;
            break;
          case '收藏量':
            $follow = $tmp_idx;
            break;
          case '浏览量':
            $pageview = $tmp_idx;
            break;
        }
        $tmp_idx++;
      }
      
      if($title === 0 || $penname === 0 || $nameremark === 0 || $register===0 || $editor === 0 || $writerinfo === 0||$represent === 0 ||$keyword === 0 ||$oneword === 0 ||$people === 0 ||$outline === 0 ||$follow === 0 ||$pageview === 0) {
        $json['error'] = '请检查文件第一行是否有：项目名称、作者、版权方、版权方登记信息、策划编辑、作者简介、代表作品、关键字、一句话介绍、人物小传、项目梗概、收藏量、浏览量';
      }
      // var_dump($json);exit();
      if (!$json) {
      $express_infos = array();
      for ($currentRow = 2;$currentRow <= $allRow;$currentRow++) {
        if(strlen($title) > 0){
          $title_tmp = (String)$currentSheet->getCell($title.$currentRow)->getValue();
          $penname_tmp = (String)$currentSheet->getCell($penname.$currentRow)->getValue();
          $nameremark_tmp = (String)$currentSheet->getCell($nameremark.$currentRow)->getValue();
          $register_tmp = (String)$currentSheet->getCell($register.$currentRow)->getValue();
          $editor_tmp = (String)$currentSheet->getCell($editor.$currentRow)->getValue();
          $writerinfo_tmp = (String)$currentSheet->getCell($writerinfo.$currentRow)->getValue();
          $represent_tmp = (String)$currentSheet->getCell($represent.$currentRow)->getValue();
          $keyword_tmp = (String)$currentSheet->getCell($keyword.$currentRow)->getValue();
          $oneword_tmp = (String)$currentSheet->getCell($oneword.$currentRow)->getValue();
          $people_tmp = (String)$currentSheet->getCell($people.$currentRow)->getValue();
          $outline_tmp = (String)$currentSheet->getCell($outline.$currentRow)->getValue();
          $follow_tmp = (String)$currentSheet->getCell($follow.$currentRow)->getValue();
          $pageview_tmp = (String)$currentSheet->getCell($pageview.$currentRow)->getValue();
          if(strlen($title_tmp)>0&&strlen($penname_tmp)>0&&strlen($nameremark_tmp)>0&&strlen($editor_tmp)>0&&strlen($outline_tmp)>0){
            $express_infos[] = array(
              'title' => $title_tmp,
              'penname' => $penname_tmp,
              'nameremark' => $nameremark_tmp,
              'register' => $register_tmp,
              'editor' => $editor_tmp,
              'writerinfo' => $writerinfo_tmp,
              'represent' => $represent_tmp,
              'keyword' => $keyword_tmp,
              'oneword' => $oneword_tmp,
              'people' => $people_tmp,
              'outline' => $outline_tmp,
              'follow' => $follow_tmp,
              'pageview' => $pageview_tmp,
            );
          }
        }
      }
      return $express_infos;
        // $this->load->model('sale/order');
        // $import_resultes = $this->model_sale_order->updateExpressInfo($express_infos);
        // $import_resultes = $this->model_sale_order->updateExpressAndCardInfo($express_infos);

        // $json['success'] = sprintf('物流状态:更新成功%s条，未更新%s条', $import_resultes['success'], $import_resultes['failed']);
      }
    }
    
    // $this->response->addHeader('Content-Type: application/json');
    // $this->response->setOutput(json_encode($json));

  }
}